package com.textmanager.db;

import com.textmanager.model.TextFile;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TextFileDAO {

    public static TextFile createFile(String name, String content, int directoryId) throws SQLException {
        String sql = "INSERT INTO files (name, content, directory_id) VALUES (?, ?, ?)";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            pstmt.setString(1, name);
            pstmt.setString(2, content);
            pstmt.setInt(3, directoryId);
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Creating file failed, no rows affected.");
            }

            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return new TextFile(generatedKeys.getInt(1), name, content, directoryId);
                } else {
                    throw new SQLException("Creating file failed, no ID obtained.");
                }
            }
        }
    }

    public static TextFile getFile(int id) throws SQLException {
        String sql = "SELECT * FROM files WHERE id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return new TextFile(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("content"),
                    rs.getInt("directory_id")
                );
            }
            return null;
        }
    }

    public static List<TextFile> getFilesInDirectory(int directoryId) throws SQLException {
        String sql = "SELECT * FROM files WHERE directory_id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, directoryId);
            ResultSet rs = pstmt.executeQuery();
            List<TextFile> files = new ArrayList<>();
            
            while (rs.next()) {
                files.add(new TextFile(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("content"),
                    rs.getInt("directory_id")
                ));
            }
            
            return files;
        }
    }

    public static boolean updateFile(TextFile file) throws SQLException {
        String sql = "UPDATE files SET name = ?, content = ?, directory_id = ? WHERE id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, file.getName());
            pstmt.setString(2, file.getContent());
            pstmt.setInt(3, file.getDirectoryId());
            pstmt.setInt(4, file.getId());
            
            return pstmt.executeUpdate() > 0;
        }
    }

    public static boolean deleteFile(int id) throws SQLException {
        String sql = "DELETE FROM files WHERE id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        }
    }

    public static boolean moveFile(int fileId, int newDirectoryId) throws SQLException {
        String sql = "UPDATE files SET directory_id = ? WHERE id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, newDirectoryId);
            pstmt.setInt(2, fileId);
            
            return pstmt.executeUpdate() > 0;
        }
    }

    public static List<TextFile> searchFilesByContent(String searchText) throws SQLException {
        String sql = "SELECT * FROM files WHERE LOWER(content) LIKE LOWER(?)";
        List<TextFile> files = new ArrayList<>();
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + searchText + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                files.add(new TextFile(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("content"),
                    rs.getInt("directory_id")
                ));
            }
        }
        return files;
    }
}